import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Scanner;


/**
 * This Query Class must be used with a localhost with the appropriate database. I have the database on my localhost and will demo the code. 
 * As a result, the code will not work as it needs a database which you may not have and we do not know how to send to you. If you like you can 
 * install xampp (windows) mamp (mac) and it will set up a localhost complete with phpmyadmin where the program can be tested. 
 * @author Daranhdara Hun
 *
 */
public class MyQuery 
{


	/**
	 * Use this method to search for a patient by full name
	 * It will return a very long string with all user attribute in the following order
	 * 1 = password		9 = name
	 * 2 = username		10 = address
	 * 3 = pharmacy		11 = email
	 * 4 = age			12 = suspend
	 * 5 = cellphone	13 = ssn
	 * 6 = phone		
	 * 7 = insurance
	 * 8 = gender			
	 * After all 13 attributes are completed, the next 13 will be the attributes of the next 
	 * patient matching the name criteria.		 
	 * @param name
	 * @return
	 * @throws Exception
	 */
	public String searchUserByName(String name) throws Exception
	{
		System.out.println("SELECT * FROM `patient` WHERE `name` LIKE " + "'"+name+"'");
		//create the query
		PreparedStatement statement = connect().prepareStatement("SELECT * FROM `patient` WHERE `name` LIKE " + "'"+name+"' LIMIT 0 , 30");

		//Creating a variable to execute query
		ResultSet result = statement.executeQuery();
		System.out.println(result);
		String patientInfo = "";

		while(result.next())
		{
			patientInfo=
				result.getString(1) + " " +  result.getString(2) + " " + result.getString(3) + " " + result.getString(4)+ " " + 
				result.getString(5) + " " +  result.getString(6) + " " + result.getString(7) + " " + result.getString(8)+ " " + 
				result.getString(9) + " " +  result.getString(10) + " " + result.getString(11) + " " + result.getString(12)+ " " + 
				result.getString(13);
			/*
			getString returns the data. Below are patient attributes.
			1 = password		9 = name
			2 = username		10 = address
			3 = pharmacy		11 = email
			4 = age				12 = suspend
			5 = cellphone		13 = ssn
			6 = phone			
			7 = insurance		 
			8 = gender			
			 */
			patientInfo = patientInfo + " " + patientInfo; 
		}

		return patientInfo;
	}

	public static Connection connect () throws Exception
	{
		//Accessing driver from the JAR file
		Class.forName("com.mysql.jdbc.Driver");

		//Creating a variable for the connection called "con"
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:8889/CS2340","root","root");
		//jdbc:mysql://localhost:8889/testdb --> database
		//DB user --> root
		//DB password --> root

		//insertStatement.executeUpdate("INSERT INTO names " + "VALUES ('Jimmy', 'johns')");
		return con;
	}

	/**
	 * Create the query statement and instance to select all attributes from the specified table
	 * and returns the results in a table.
	 * @param table
	 * @return a ResultSet which is s table of the results from the table
	 * @throws Exception
	 */
	public ResultSet getTableRow(String table) throws Exception
	{
		//create the query
		PreparedStatement statement = connect().prepareStatement("select * from " + table);

		//Creating a variable to execute query
		ResultSet result = statement.executeQuery();

		return result;

	}

	/**
	 * STATIC METHOD -- Will deliver a string of attributes in order.
	 * @param ssn - social security number of a patient needed to access the database
	 * @return patientInfo - a string of all attributes for a particular patient
	 * @throws Exception
	 */
	public static String loadUser(int ssn) throws Exception
	{
		//create the query
		PreparedStatement statement = connect().prepareStatement("SELECT * FROM patient WHERE ssn=" + ssn);

		//Creating a variable to execute query
		ResultSet result = statement.executeQuery();

		String patientInfo = "";

		while(result.next())
		{
			patientInfo=
				result.getString(1) + " " +  result.getString(2) + " " + result.getString(3) + " " + result.getString(4)+ " " + 
				result.getString(5) + " " +  result.getString(6) + " " + result.getString(7) + " " + result.getString(8)+ " " + 
				result.getString(9) + " " +  result.getString(10) + " " + result.getString(11) + " " + result.getString(12)+ " " + 
				result.getString(13);
			/*
			getString returns the data. Below are patient attributes.
			1 = password		9 = name
			2 = username		10 = address
			3 = pharmacy		11 = email
			4 = age				12 = suspend
			5 = cellphone		13 = ssn
			6 = phone			
			7 = insurance		 
			8 = gender			
			 */
		}
		return patientInfo;
	}




	/**
	 * Saves Patient informatin into database for the first time
	 * Employee will call this method to save patient information
	 * @param ssn - Employee must provide social security number 
	 * @throws Exception
	 */
	public void saveUser(User user, int ssn) throws SQLException,Exception
	{
		Statement stmt = null;


		/**/
		String password = user.getPassword();
		String username = user.getUserName();
		String pharmacy = user.getPharmacy();
		Date age = user.getBirthDate();
		String cellphone = user.getCellPhone();
		String phone = user.getPhone();
		String insurance = user.getInsurance();
		Enum gender = user.getGender();
		String name = user.getName();
		String address = user.getAddress();
		String email = user.getEmail();
		Boolean suspended = user.getSuspended();
		/*String ssn = user.getSSN();*/

		try{
			stmt = connect().createStatement();
			stmt.executeUpdate("INSERT INTO `CS2340`.`patient` " +
					"(`password`, `username`, `pharmacy`, `age`, `cellphone`, `phone`, `insurance`, `gender`, `name`, `address`, " +
					"`email`, `suspended`, `ssn`) " +
					"VALUES " +
					"(`"+password+"`, `"+username+"`, `"+pharmacy+"`, `"+age+"`, `"+cellphone+"`, `"+phone+"`, `"+insurance+"`, `"+gender+"`, `"+name+"`, `"+address+"`, " +
					"`"+email+"`, `"+suspended+"`, `"+ssn+"`);");

			/*
					getString returns the data. Below are patient attributes.
					1 = password		9 = name
					2 = username		10 = address
					3 = pharmacy		11 = email
					4 = age				12 = suspend
					5 = cellphone		13 = ssn
					6 = phone			
					7 = insurance		 
					8 = gender			
			 */
		}
		finally{
			stmt.close();
		}
	}

	/**
	 * Use this method to update a users information
	 * @param ssn - must use SSN number when using this method
	 * @throws SQLException
	 * @throws Exception
	 */
	public void updateUser(User user, int ssn) throws SQLException,Exception
	{
		Statement stmt = null;

		/**/
		String password = user.getPassword();
		String username = user.getUserName();
		String pharmacy = user.getPharmacy();
		Date age = user.getBirthDate();
		String cellphone = user.getCellPhone();
		String phone = user.getPhone();
		String insurance = user.getInsurance();
		Enum gender = user.getGender();
		String name = user.getName();
		String address = user.getAddress();
		String email = user.getEmail();
		//				Boolean suspended = true;
		Boolean suspended = user.getSuspended();
		/*String ssn = user.getSSN();*/


		/*
				UPDATE  `CS2340`.`patient` SET  `password` =  'testfgfg',
				`username` =  'testfg',
				`pharmacy` =  'testfg',
				`cellphone` =  'fg',
				`phone` =  'fg',
				`insurance` =  'teser',
				`gender` =  'FEMALE',
				`name` =  'testgfg',
				`address` =  'testfg',
				`email` =  'testggf',
				`suspended` =  '0',
		 */
		try{
			stmt = connect().createStatement();
			stmt.executeUpdate
			(
					"UPDATE `CS2340`.`patient` SET (" +
					"`password` = `"+password+"`, " +
					"`username` = `"+username+"`, " +
					"`pharmacy` = `"+pharmacy+"`, " +
					"`age` = `"+age+"`, " +
					"`cellphone` = `"+cellphone+"`, " +
					"`phone` = `"+phone+"`, " +
					"`insurance` = `"+insurance+"`, " +
					"`gender` = `"+gender+"`, " +
					"`name` = `"+name+"`, " +
					"`address` = `"+address+"`, " +
					"`email` = `"+email+"`, " +
					"`suspended` = `"+suspended+"`, " +
					"`ssn` = `"+ssn+"`) "
			);

			/*
					getString returns the data. Below are patient attributes.
					1 = password		9 = name
					2 = username		10 = address
					3 = pharmacy		11 = email
					4 = age				12 = suspend
					5 = cellphone		13 = ssn
					6 = phone			
					7 = insurance		 
					8 = gender			
			 */
		}
		finally{
			stmt.close();
		}
	}

	/**
	 * STATIC METHOD -- Deletes a user never to return again.
	 * @param ssn - must specify user by SSN
	 * @throws SQLException
	 * @throws Exception
	 */
	public static void deleteUser(int ssn) throws SQLException,Exception
	{
		Statement stmt = null;

		/*
		DELETE FROM `CS2340`.`patient` WHERE `patient`.`ssn` = 123456789
		 */
		try{ 
			stmt = connect().createStatement();
			stmt.executeUpdate
			(
					"DELETE FROM `CS2340`.`patient` WHERE `patient`.'ssn' = "+ssn
			);
		}
		finally{
			stmt.close();
		}
	}

	/**
	 * 
	 * @param password
	 * @param username
	 * @return
	 * @throws Exception
	 */
	public boolean authenticateUser(String password, String username) throws Exception
	{
		System.out.println("SELECT * FROM patient WHERE username='" + username + "'");
		/*create the query*/

		PreparedStatement statement = connect().prepareStatement("SELECT * FROM `patient` WHERE `username` LIKE '"+username+"'");

		/*Creating a variable to execute query*/
		ResultSet result = statement.executeQuery();

		String dbPassword = "";

		while(result.next())
		{
			dbPassword=
				result.getString(1);

			/*
			getString returns the data. Below are patient attributes.
			1 = password		9 = name
			2 = username		10 = address
			3 = pharmacy		11 = email
			4 = age				12 = suspend
			5 = cellphone		13 = ssn
			6 = phone			
			7 = insurance		 
			8 = gender			
			 */ 
		}

		if (password.equals(dbPassword))
			return true; /*Correct*/
		else return false; /*Incorrect username and Password*/
	}

	

	/**
	 * You must specify the username first.
	 * Usernames are unique
	 * @param username
	 * @return int -- this will be the patients SSN with no dashes or spaces between the numbers
	 * @throws SQLException
	 * @throws Exception
	 */
	public int getSSN(String username) throws SQLException, Exception
	{
		//create the query
		PreparedStatement statement = connect().prepareStatement("SELECT * FROM `patient` WHERE `username` LIKE '"+username+"'");


		//Creating a variable to execute query
		ResultSet result = statement.executeQuery();
		System.out.println(result);
		String socialNum = "";

		while(result.next())
		{
			socialNum = result.getString(13);
			/*
				getString returns the data. Below are patient attributes.
				1 = password		9 = name
				2 = username		10 = address
				3 = pharmacy		11 = email
				4 = age				12 = suspend
				5 = cellphone		13 = ssn
				6 = phone			
				7 = insurance		 
				8 = gender			
			 */

		}

		return Integer.parseInt(socialNum);
	}

	/**
	 * Creates a User object. 
	 * Use this method when accessing User Information from the database
	 * A Nurse or Doctor will use this method whenever he pulls up information on a patient
	 * @param username
	 * @return User
	 */
	public User createUser(String username)
	{
		User user = new User();
		int tempSSN = 0;
		String userInfoString = "";
		String temporary;
		Date birthdate;

		try 
		{
			tempSSN = getSSN(username);
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}

		try 
		{
			userInfoString = loadUser(tempSSN);
		} catch (Exception e) 
		{
			e.printStackTrace();
		}

		Scanner scanner = new Scanner(userInfoString);
		user.setPassword(scanner.next());
		user.setUserName(scanner.next());
		user.setPharmacy(scanner.next());
		/*Convert String date to date*/
		temporary = scanner.next();
		DateFormat formatter;
		formatter = new SimpleDateFormat("dd-MM-yy");
		birthdate = (Date)formatter.parse(temporary);
		user.setBirthDate(birthdate);
		
		user.setCellPhone(scanner.next());
		user.setPhone(scanner.next());
		user.setInsurance(scanner.next());
		/*Convert String enum to true java Enum*/
		if (scanner.next().equals("Male"))
		{
			user.setGender(Gender.Male);
		}
		else user.setGender(Gender.Female);

		user.setName(scanner.next());
		user.setAddress(scanner.next());
		user.setEmail(scanner.next());
		
		user.setSuspended(convertStringToBoolean(scanner.next()));
		user.setSSN(scanner.next());

		return  user;
		/*
		getString returns the data. Below are patient attributes.
		1 = password		9 = name
		2 = username		10 = address
		3 = pharmacy		11 = email
		4 = age				12 = suspend
		5 = cellphone		13 = ssn
		6 = phone			
		7 = insurance		 
		8 = gender			
		 */
	}
	
	/**
     * String to boolean conversion
     */
    public boolean convertStringToBoolean(String booleanString) {
        
        String strBoolean = booleanString;
        
        /*Do the String to boolean conversion*/
        boolean theValue = Boolean.parseBoolean(strBoolean);
        
        return theValue;
    }


	public static void main (String[] args) throws Exception
	{	
		//		MyQuery newquery = new MyQuery();
		//		Boolean newstring = newquery.authenticateUser("ERd","DFG");
		//		System.out.println(newstring);
//				MyQuery newquery = new MyQuery();
//				String newstring = newquery.searchUserByName("RTY");
//				System.out.println(newstring);
//				MyQuery newquery = new MyQuery();
//				int newstring = newquery.getSSN("DFG");
//				System.out.println("Should be SSN: " + newstring);
//				MyQuery newquery = new MyQuery();
//				boolean newstring = newquery.authenticateUser("ER","DFG");
//				System.out.println("Should be SSN: " + newstring);
		MyQuery newquery = new MyQuery();
		User newstring = newquery.createUser("DFG");
		System.out.println("Should be SSN: " + newstring);
		
	}
	//Save Apppointments and Patient Invoices 

}






